-- =============================================
-- Author:		Leung
-- Create date: 01/02/2010
-- =============================================
USE TimeSheet
GO
IF OBJECT_ID('[dbo].[usp_TSExportXML]') IS NOT NULL
BEGIN 
    DROP PROC [dbo].[usp_TSExportXML] 
END 
GO
CREATE PROC usp_TSExportXML
	@TimesheetIds VARCHAR(MAX)
AS
BEGIN 
SET NOCOUNT ON
DECLARE @ids AS TABLE(val BIGINT)
INSERT INTO @ids 
SELECT CAST(val AS BIGINT) AS 'val' FROM dbo.fn_Split(@TimesheetIds,', ')

SELECT     users.UserId 'User/@ID' ,
users.Username 'User', 
role.RoleName AS 'Role',
membership.FirstName ,
membership.LastName,
( 
SELECT 
ts.TimeSheetId '@Id',
CAST(ts.Date AS VARCHAR(12)) 'Date',
STR(ts.Time,4,2) 'Time',
ts.Description ,
p.ProductName 'Product',
ps.ProcessName 'Process',
w.WorkName 'Work',
s.StatusName 'Status', 
pj.ProjectName 'Project' 
FROM         ts_timesheet AS ts INNER JOIN
                      ts_product AS p ON ts.ProductId = p.ProductId INNER JOIN
                      ts_work AS w ON ts.WorkId = w.WorkId INNER JOIN
                      ts_process AS ps ON ts.ProcessId = ps.ProcessId INNER JOIN
                      ts_status AS s ON ts.StatusId = s.StatusId INNER JOIN
                      pj_project AS pj ON ts.ProjectId = pj.ProjectId INNER JOIN
					  @ids AS id ON id.val = ts.TimesheetId
ORDER BY ts.Date,ts.Time 
FOR XML PATH('Record') , ROOT('TimeSheetRecords') , TYPE
)         
FROM         ts_timesheet INNER JOIN
                      users ON ts_timesheet.UserId = users.UserId INNER JOIN
					  @ids AS id ON id.val = ts_timesheet.TimesheetId INNER JOIN	
                      [role] ON users.RoleId = role.RoleId LEFT JOIN
					  membership ON users.UserId = membership.UserId 
GROUP BY
users.UserId,
users.Username, role.RoleName,
membership.FirstName ,
membership.LastName
FOR XML PATH('TimeSheet'), ROOT('TimeSheets') , TYPE
END
/*
 * Function split string
 */
CREATE FUNCTION fn_Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(5)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

